package com.demo.utils;

import cn.lingyangwl.framework.tool.core.exception.BizException;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * 基于Jsqlparser的sql解析功能
 */
public class JsqlparserHelper {
    private static final Logger log = LoggerFactory.getLogger(JsqlparserHelper.class);
    /* 原sql */
    private String sql;
    private Select select;
    private PlainSelect plainSelect;
    public JsqlparserHelper(String sql) throws JSQLParserException {
        this.sql = sql;
        this.select = (Select) CCJSqlParserUtil.parse(this.sql);
        this.plainSelect = (PlainSelect)select.getSelectBody();

    }

    /**
     * 测试类
     */
    public static void main(String[] args) throws JSQLParserException {
//        String sql = "select * from test_table where a=1 group by c";
//        String sql = "select * from test_table where a=1 group by c";
//        String sql = "select job_id, job_name, job_group, invoke_target, cron_expression, misfire_policy, concurrent, status, create_by, create_time, remark from sys_job " +
//                "left join test on test.id = sys_job.user_id where test = 1";
//        String sql = "select job_id, job_name, job_group, invoke_target, cron_expression, misfire_policy, concurrent, status, create_by, create_time, remark from sys_job ss " +
//                "where test = 1";
//        String sql = "select count(*) from test";
        String sql = "select MAX(test.id) from test";
        JsqlparserHelper helper = new JsqlparserHelper(sql);
        helper.isFunctionCall();
        List<String> tableNameList = helper.getAllTableName();
        System.out.println("tableName:" + tableNameList);
        if (null != tableNameList) {
            String columnNames = helper.getColumnNames();
            System.out.println("columnNames:" + columnNames);
        }

        int count = 1;
        while (count-- > 0) {
            if (CollectionUtils.isEmpty(tableNameList)) {
                throw new BizException("from 表名为空");
            }
            long start = System.currentTimeMillis();
            helper.addWhereConditionForAndExpression("( b = 1 and c = 4)");
            System.out.println(helper.getTargetSql());
            helper.addInnerJoinToForward("sys_user_dept_rel", "sys_user_dept_rel_alias", "user_id", "user_id");
            System.out.println(helper.getTargetSql());
            System.out.println(helper.getFromTableAlias());
            System.out.println("time: " + (System.currentTimeMillis() - start));
        }
    }

    /**
     * 判断是否为函数调用
     * eg1: sql = select count(*) from test, return true
     * eg2: sql = select count(*) from test, return true
     *
     * @return true: 函数调用
     */
    public boolean isFunctionCall() {
        if (plainSelect.getSelectItems().get(0) instanceof SelectExpressionItem) {
            return ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression() instanceof Function;
        }
       return false;
    }

    public String getSql() {
        return sql;
    }

    /**
     * 获取目标sql
     */
    public String getTargetSql() {
        return plainSelect.toString();
    }
    /**
     * 多表SQL查询
     * JOIN / INNER JOIN: 如果表中有至少一个匹配，则返回行
     * LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
     * RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
     * FULL JOIN: 只要其中一个表中存在匹配，就返回行
     */
    public void addInnerJoinToForward(String joinTableName, String joinTableAlias, String leftColumnName, String rightColumnName) {
        // 右侧表名
        Table joinTable = new Table(joinTableName).withAlias(new Alias(joinTableAlias).withUseAs(true));
        // fromTable
        Table fromTable;
        String fromTableAlias = getFromTableAlias();
        if (StringUtils.isEmpty(fromTableAlias)) {
            fromTable = new Table(getFromTableName());
        } else {
            fromTable = new Table(fromTableAlias);
        }


        // JOIN ON 如果表中有至少一个匹配，则返回行
        // 设置join参数可实现其他类型join
        // join.setLeft(true); LEFT JOIN
        // join.setRight(true);  RIGHT JOIN
        // join.setFull(true); FULL JOIN
        // join.setInner(true);
        Join join = new Join();
        join.setInner(true);
        join.withRightItem(joinTable); // 添加Join的表
        EqualsTo equalsTo = new EqualsTo(); // 添加 = 条件表达式  t1.user_id  = t2.user_id
        equalsTo.setLeftExpression(new Column(joinTable, leftColumnName));
        equalsTo.setRightExpression(new Column(fromTable, rightColumnName));
        join.withOnExpression(equalsTo);// 添加ON

        List<Join> joinList = plainSelect.getJoins();
        if (joinList == null) {
            joinList = new ArrayList<>();
        }
        joinList.add(0, join);
        plainSelect.setJoins(joinList);
    }

    /**
     * 获取SQL中的from表名
     */
    public String getFromTableName() {
        if (plainSelect.getFromItem() instanceof Table) {
            Table table = (Table)plainSelect.getFromItem();
            return table.getName();
        }
        return "";
    }
    /**
     * 获取SQL中的from表名
     */
    public String getFromTableAlias() {
        Alias alias = plainSelect.getFromItem().getAlias();
        if (alias != null) {
            return alias.getName();
        }
        return "";
    }

    /**
     * 获取SQL中的全部表名
     */
    public List<String> getAllTableName() throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(this.sql);
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        return tablesNamesFinder.getTableList(statement);
    }

    /**
     * 在where后面添加条件
     * @param condition "a=1 and b=2"
     */
    public void addWhereConditionForAndExpression(String condition) {
        try {
            final Expression expression = plainSelect.getWhere();
            final Expression envCondition = CCJSqlParserUtil.parseCondExpression(condition);
            if (expression == null) {
                plainSelect.setWhere(envCondition);
            } else {
                AndExpression andExpression = new AndExpression(expression, envCondition);
                plainSelect.setWhere(andExpression);
            }
        } catch (JSQLParserException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 对where后的条件语句封装, 过滤掉已经被删除的数据
     * @param condition
     * @param deleteFlagName 数据库中删除标志名称
     */
    public String addWhereConditionForFilterDeletedData(String condition, String deleteFlagName) {
        Table fromTable;
        String fromTableAlias = getFromTableAlias();
        if (StringUtils.isEmpty(fromTableAlias)) {
            fromTable = new Table(getFromTableName());
        } else {
            fromTable = new Table(fromTableAlias);
        }
        return "( " + condition + " and " + fromTable.getName() + "." + deleteFlagName + " = "  + 0 + " )";
    }

    /**
     * 在where后面添加条件
     * @param condition "a=1 and b=2"
     */
    public void addWhereConditionForOrExpression(String condition) {
        try {
            final Expression expression = plainSelect.getWhere();
            final Expression envCondition = CCJSqlParserUtil.parseCondExpression(condition);
            if (expression == null) {
                plainSelect.setWhere(envCondition);
            } else {
                OrExpression orExpression = new OrExpression(expression, envCondition);
                plainSelect.setWhere(orExpression);
            }
        } catch (JSQLParserException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取SQL中的where后面的条件名称
     */
    public String getColumnNames() throws JSQLParserException {
        String columnNames = null;
        String allColumnNames = null;
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(sql);
        Statement statement = CCJSqlParserUtil.parse(new StringReader(stringBuffer.toString()));
        if (statement instanceof Select) {
            Select istatement = (Select) statement;
            Expression where = ((PlainSelect) istatement.getSelectBody()).getWhere();
            if (null != where) {
                Set<String> sets = getParser(where);
                StringBuffer st = new StringBuffer();
                sets.forEach(set -> {
                    st.append(set).append(",");
                });
                columnNames = st.toString();
            }
        }
        if (null != columnNames && !columnNames.equals("")) {
            allColumnNames = columnNames.substring(0, columnNames.length() - 1);
        }
        return allColumnNames;
    }

    public Set<String> getParser(Expression expression) {
        Set<String> columnNameSet = new HashSet<>();
        return getParser(expression, columnNameSet);
    }

    private Set<String> getParser(Expression expression, Set<String> columnNameSet) {
        //初始化接受获得的字段信息
        if (expression instanceof BinaryExpression) {
            //获得左边表达式
            Expression leftExpression = ((BinaryExpression) expression).getLeftExpression();
            //获得左边表达式为Column对象，则直接获得列名
            if (leftExpression instanceof Column) {
                columnNameSet.add(((Column) leftExpression).getColumnName());
            } else if (leftExpression instanceof InExpression) {
                columnNameSet.addAll(parserInExpression(leftExpression));
            } else if (leftExpression instanceof IsNullExpression) {
                columnNameSet.addAll(parserIsNullExpression(leftExpression));
            } else if (leftExpression instanceof BinaryExpression) {
                // 递归调用
                getParser(leftExpression);
            } else if (expression instanceof Parenthesis) {
                // 递归调用
                Expression expression1 = ((Parenthesis) expression).getExpression();
                getParser(expression1);
            }

            //获得右边表达式，并分解
            Expression rightExpression = ((BinaryExpression) expression).getRightExpression();
            if (rightExpression instanceof BinaryExpression) {
                columnNameSet.addAll(parserBinaryExpression(rightExpression));
            } else if (rightExpression instanceof InExpression) {
                columnNameSet.addAll(parserInExpression(rightExpression));
            } else if (rightExpression instanceof IsNullExpression) {
                columnNameSet.addAll(parserIsNullExpression(rightExpression));
            } else if (rightExpression instanceof Parenthesis) {//递归调用
                Expression expression1 = ((Parenthesis) rightExpression).getExpression();
                getParser(expression1);
            }
        } else if (expression instanceof InExpression) {
            columnNameSet.addAll(parserInExpression(expression));
        } else if (expression instanceof IsNullExpression) {
            columnNameSet.addAll(parserIsNullExpression(expression));
        } else if (expression instanceof Parenthesis) {//递归调用
            Expression expression1 = ((Parenthesis) expression).getExpression();
            getParser(expression1);
        }
        return columnNameSet;
    }

    /**
     * 解析in关键字左边的条件
     *
     * @param expression
     * @return ColumnName
     */
    public Set<String> parserInExpression(Expression expression) {
        Set<String> columnNameSet = new HashSet<>();
        Expression leftExpression = ((InExpression) expression).getLeftExpression();
        if (leftExpression instanceof Column) {
            columnNameSet.add(((Column) leftExpression).getColumnName());
        }
        return columnNameSet;
    }

    /**
     * 解析is null 和 is not null关键字左边的条件
     *
     * @param expression
     */
    public Set<String> parserIsNullExpression(Expression expression) {
        Set<String> columnNameSet = new HashSet<>();
        Expression leftExpression = ((IsNullExpression) expression).getLeftExpression();
        if (leftExpression instanceof Column) {
            columnNameSet.add(((Column) leftExpression).getColumnName());
        }
        return columnNameSet;
    }

    public Set<String> parserBinaryExpression(Expression expression) {
        Set<String> columnNameSet = new HashSet<>();
        Expression leftExpression = ((BinaryExpression) expression).getLeftExpression();
        if (leftExpression instanceof Column) {
            columnNameSet.add(((Column) leftExpression).getColumnName());
        }
        return columnNameSet;
    }
}
